/**
 * 
 */
package run;

import java.beans.Statement;
import java.sql.*;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.JoinRowSet;

import com.sun.rowset.CachedRowSetImpl;
import com.sun.rowset.JoinRowSetImpl;

import database.SetConn;

/**
 * @author ys
 * 
 */
public class Search {
	private Connection conn = null;
	private int count;
	private String[][] detail;
	private ResultSet rs;

	public String[][] getDetail() {
		return detail;
	}

	public int getCount() {
		return count;
	}

	public ResultSet getResultSet() {
		return rs;
	}

	public void setResultSet(ResultSet rs) {
		this.rs = rs;
	}

	private void getString(ResultSet rs, int count) {
		try {
			System.out.println(count);
			detail = new String[count][4];
			int i = 0;
			while (rs.next()) {
				detail[i][0] = rs.getString(4);
				detail[i][1] = rs.getString(5);
				detail[i][2] = Float.toString(rs.getFloat(6));
				detail[i][3] = rs.getString(8);
				i++;
			}
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

	private void getString(JoinRowSet rs, int count) {
		try {
			System.out.println(count);
			detail = new String[count][4];
			int i = 0;
			while (rs.next()) {
				detail[i][0] = rs.getString(4);
				detail[i][1] = rs.getString(5);
				detail[i][2] = Float.toString(rs.getFloat(6));
				detail[i][3] = rs.getString(8);
				i++;
			}
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

	public void getCommodityBySmallClass(int ClassID) {
		try {
			// ResultSet rs;
			SetConn testconn = new SetConn();
			conn = testconn.setupConn();
			String sqlCount = "select count(*) from commodity where ClassID=?";
			String sql = "select * from commodity where ClassID=? ";
			PreparedStatement queryPs = conn.prepareStatement(sqlCount);
			queryPs.setInt(1, ClassID);
			rs = queryPs.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			} else {
				return;
			}
			queryPs = conn.prepareStatement(sql);
			queryPs.setInt(1, ClassID);
			rs = queryPs.executeQuery();
			getString(rs, count);
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

	public void getCommodityByClass(int class_one, int class_two,
			ResultSet old_rs) {
		try {
			// ResultSet rs;
			SetConn testconn = new SetConn();
			conn = testconn.setupConn();
			String sqlCount = "select count(*) from commodity where ClassID between ? and ?";
			String sql = "select * from commodity where ClassID between ? and ? ";
			PreparedStatement queryPs = conn.prepareStatement(sqlCount);
			queryPs.setInt(1, class_one);
			queryPs.setInt(2, class_two);
			rs = queryPs.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			} else {
				return;
			}
			System.out.println("count:" + count);
			queryPs = conn.prepareStatement(sql);
			queryPs.setInt(1, class_one);
			queryPs.setInt(2, class_two);
			rs = queryPs.executeQuery();

			JoinRowSet jrs = new JoinRowSetImpl();
			int[] matchColumn = { 1, 2, 3, 4 };
			CachedRowSet empl = new CachedRowSetImpl();
			empl.populate(rs);
			empl.setMatchColumn(matchColumn);
			jrs.addRowSet(empl);

			CachedRowSet bonus = new CachedRowSetImpl();
			bonus.populate(old_rs);
			bonus.setMatchColumn(matchColumn);
			jrs.addRowSet(bonus);

			getString(jrs, count);
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

	public void getCommodityByClass(int class_one, int class_two) {
		try {
			// ResultSet rs;
			SetConn testconn = new SetConn();
			conn = testconn.setupConn();
			String sqlCount = "select count(*) from commodity where ClassID between ? and ?";
			String sql = "select * from commodity where ClassID between ? and ? ";
			PreparedStatement queryPs = conn.prepareStatement(sqlCount);
			queryPs.setInt(1, class_one);
			queryPs.setInt(2, class_two);
			rs = queryPs.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			} else {
				return;
			}
			queryPs = conn.prepareStatement(sql);
			queryPs.setInt(1, class_one);
			queryPs.setInt(2, class_two);
			rs = queryPs.executeQuery();
			getString(rs, count);
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

	public void getCommodityByPrice(int lowerPrice, int upperPrice) {
		try {
			// ResultSet rs;
			SetConn testconn = new SetConn();
			conn = testconn.setupConn();
			String sqlCountHotPrice = "(select count(*) from commodity where HotPrice>? and HotPrice<=? and IsDiscount='Y')";
			String sqlCountMarketPrice = "(select  count(*) from commodity where MarketPrice>? and MarketPrice<=? and IsDiscount='N')";
			String sql = "(select * from commodity where HotPrice>? and HotPrice<=? and IsDiscount='Y') union (select * from commodity where MarketPrice>? and MarketPrice<=? and IsDiscount='N')";
			PreparedStatement queryPs = conn.prepareStatement(sqlCountHotPrice);
			queryPs.setInt(1, lowerPrice);
			queryPs.setInt(2, upperPrice);
			System.out.println("lowerPrice:" + lowerPrice);
			System.out.println("upperPrice:" + upperPrice);
			System.out.println("sqlCountHotPrice:" + sqlCountHotPrice);
			rs = queryPs.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			} else {
				return;
			}
			queryPs = conn.prepareStatement(sqlCountMarketPrice);
			queryPs.setInt(1, lowerPrice);
			queryPs.setInt(2, upperPrice);
			System.out.println("sqlCountMarketPrice:" + sqlCountMarketPrice);
			rs = queryPs.executeQuery();
			if (rs.next()) {
				count += rs.getInt(1);
			} else {
				return;
			}
			queryPs = conn.prepareStatement(sql);
			queryPs.setInt(1, lowerPrice);
			queryPs.setInt(2, upperPrice);
			queryPs.setInt(3, lowerPrice);
			queryPs.setInt(4, upperPrice);
			System.out.println("sql:" + sql);
			rs = queryPs.executeQuery();
			getString(rs, count);
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

	public void getCommodityByName(String commodityName) {
		try {
			// ResultSet rs;
			SetConn testconn = new SetConn();
			conn = testconn.setupConn();
			String sqlCount = "select count(*) from commodity where CommodityName like '%"
					+ commodityName + "%'";
			String sql = "select * from commodity where CommodityName like '%"
					+ commodityName + "%'";
			java.sql.Statement stmt = conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sqlCount);
			if (rs.next()) {
				count = rs.getInt(1);
			} else {
				return;
			}
			rs = stmt.executeQuery(sql);
			getString(rs, count);
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

	public void getCommodity(String smallClass, String mainClass,
			String commodityName, String lowerPrice, String upperPrice) {
		try {
			// ResultSet rs;
			SetConn testconn = new SetConn();
			conn = testconn.setupConn();
			String sqlCount = "select count(*) from commodity where ";
			String sql = "select * from commodity where ";
			boolean flag = false;
			// if (!smallClass.isEmpty()) {
			if (smallClass != null) {
				sqlCount += " ClassID=" + Integer.parseInt(smallClass);
				sql += " ClassID=" + Integer.parseInt(smallClass);
				flag = true;
			} else if (mainClass != null) {
				int startClassID = (Integer.parseInt(mainClass) - 1) * 9 + 1;
				int endClassID = Integer.parseInt(mainClass) * 9;
				sqlCount += " ClassID>=" + startClassID + " and ClassID<="
						+ endClassID;
				sql += " ClassID>=" + startClassID + " and ClassID<=" + endClassID;
				flag = true;
			}
			if (commodityName != null) {
				if (flag = true) {
					sqlCount += " and ";
					sql += " and ";
				}
				sqlCount += " CommodityName like '%" + commodityName + "%'";
				sql += " CommodityName like '%" + commodityName + "%'";
			}
			if (lowerPrice != null && upperPrice != null) {
				if (flag = true) {
					sqlCount += " and ";
					sql += " and ";
				}
				int lower = Integer.parseInt(lowerPrice);
				int upper = Integer.parseInt(upperPrice);
				sqlCount += " (HotPrice>=" + lower + " and HotPrice<=" + upper
						+ " and IsDiscount='Y') or (MarketPrice>" + lower
						+ " and MarketPrice<=" + upper + " and IsDiscount='N')";
				sql += " (HotPrice>=" + lower + " and HotPrice<=" + upper
						+ " and IsDiscount='Y') or (MarketPrice>" + lower
						+ " and MarketPrice<=" + upper + " and IsDiscount='N')";

			}
			System.out.println("sqlCount" + sqlCount);
			System.out.println("sql" + sql);
			PreparedStatement queryPs = conn.prepareStatement(sqlCount);
			rs = queryPs.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			} else {
				return;
			}
			queryPs = conn.prepareStatement(sql);
			rs = queryPs.executeQuery();
			getString(rs, count);
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}
	
	public void getCommodity(SearchCondition searchCondition) {
		try {
			// ResultSet rs;
			SetConn testconn = new SetConn();
			conn = testconn.setupConn();
			String sqlCount = "select count(*) from commodity where ";
			String sql = "select * from commodity where ";
			boolean flag = false;
			// if (!smallClass.isEmpty()) {
			if (searchCondition.smallClass != null) {
				sqlCount += " ClassID=" + Integer.parseInt(searchCondition.smallClass);
				sql += " ClassID=" + Integer.parseInt(searchCondition.smallClass);
				flag = true;
			} else if (searchCondition.mainClass != null) {
				int startClassID = (Integer.parseInt(searchCondition.mainClass) - 1) * 9 + 1;
				int endClassID = Integer.parseInt(searchCondition.mainClass) * 9;
				sqlCount += " ClassID>=" + startClassID + " and ClassID<="
						+ endClassID;
				sql += " ClassID>=" + startClassID + " and ClassID<=" + endClassID;
				flag = true;
			}
			if (searchCondition.commodityName != null) {
				if (flag == true) {
					sqlCount += " and ";
					sql += " and ";
				}
				sqlCount += " CommodityName like '%" + searchCondition.commodityName + "%'";
				sql += " CommodityName like '%" + searchCondition.commodityName + "%'";
				flag = true;
			}
			if (searchCondition.lowerPrice != null && searchCondition.upperPrice != null) {
				if (flag == true) {
					sqlCount += " and ";
					sql += " and ";
				}
				int lower = Integer.parseInt(searchCondition.lowerPrice);
				int upper = Integer.parseInt(searchCondition.upperPrice);
				sqlCount += " ( (HotPrice>=" + lower + " and HotPrice<=" + upper
						+ " and IsDiscount='Y') or (MarketPrice>" + lower
						+ " and MarketPrice<=" + upper + " and IsDiscount='N') ) ";
				sql += " ( (HotPrice>=" + lower + " and HotPrice<=" + upper
						+ " and IsDiscount='Y') or (MarketPrice>" + lower
						+ " and MarketPrice<=" + upper + " and IsDiscount='N') ) ";

			}
			System.out.println("sqlCount" + sqlCount);
			System.out.println("sql" + sql);
			PreparedStatement queryPs = conn.prepareStatement(sqlCount);
			rs = queryPs.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			} else {
				return;
			}
			queryPs = conn.prepareStatement(sql);
			rs = queryPs.executeQuery();
			getString(rs, count);
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

	public void getCommodityByField(String fieldName, String value) {
		try {
			// ResultSet rs;
			SetConn testconn = new SetConn();
			conn = testconn.setupConn();
			String sqlCount = "select count(*) from commodity where "
					+ fieldName + " like '%" + value + "%'";
			String sql = "select * from commodity where " + fieldName
					+ " like '%" + value + "%'";
			// PreparedStatement queryPs = conn.prepareStatement(sqlCount);
			// queryPs.setInt(1, ClassID);
			java.sql.Statement stmt = conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			// rs = queryPs.executeQuery();
			rs = stmt.executeQuery(sqlCount);
			if (rs.next()) {
				count = rs.getInt(1);
			} else {
				return;
			}
			// queryPs = conn.prepareStatement(sql);
			// queryPs.setInt(1, ClassID);
			// rs = queryPs.executeQuery();
			// GetString(rs, count);
			rs = stmt.executeQuery(sql);
			getString(rs, count);
			/*
			 * int i = 0; while (rs.next()) { for (int j = 0; j < 11; j++) { //
			 * some field may not be String detail[i][j] = rs.getString(j + 1);
			 * } i++; }
			 */
			return;
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

}
